<?php
error_reporting(E_ALL^E_NOTICE);
$tz = ini_get('date.timezone');
if(!$tz) ini_set('date.timezone', 'Asia/Shanghai');
class oracle4php4{
	var $conn;
	var $debug = false;
	var $mode = OCI_COMMIT_ON_SUCCESS;
	/*fn database_c{{{*/
	function oracle4php4($user='pps_core', $password='pps_core', $server='pps_core', $chartset='gbk'){
		$db = array(
			'Username' => $user,
			'Password' => $password,
			'Hostname' => $server,
			//'Database' => 'list',
			//'Charset' => 'AL32UTF8',
			'Charset' => in_array(strtoupper($charset), array('UTF-8', 'UTF8')) ? 'AL32UTF8' : 'zhs16gbk',
		);
		$this->conn = ocilogon(
			$db['Username'],
			$db['Password'],
			$db['Hostname'],
			$db['Charset']
		);
		if (!$this->conn) {
			$e = ocierror();
			die(htmlentities($e['message']));
		}
		register_shutdown_function(array($this,'__destruct'));
	}
	/*}}}*/
    /*fn __construct{{{*/
	/*
	function __construct(){
		$db = array(
			'Username' => 'pps_core',
			'Password' => 'pps_core',
			'Hostname' => 'pps_core',
			//'Database' => 'list',
			//'Charset' => 'AL32UTF8',
			'Charset' => 'zhs16gbk',
		);
		$this->conn = ocilogon(
			$db['Username'],
			$db['Password'],
			$db['Hostname'],
			$db['Charset']
		);
		if (!$this->conn) {
			$e = ocierror();
			die(htmlentities($e['message']));
			//trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
		}
		register_shutdown_function(array($this,'__destruct'));
    }
	 */
    /*}}}*/
    /*fn __destruct{{{*/
	function __destruct(){
		if( gettype($this->conn) == 'resource' ){
			ocilogoff($this->conn);
		}
	}
    /*}}}*/

	/*fn print_sql{{{*/
	function print_sql($sql, $param, $tag){
		if($tag) $color = 'green';
		else $color = 'red';
		if(!$this->debug) return;
		elseif($this->debug==='web'){
			echo '
<pre style="padding:0:margin:0;">
	<div style="border:3px double black; padding:0 8px 8px 8px; margin:2px;">
		<span style="color:'.$color.';">'.$sql.'</span>
		<span style="color:'.$color.';">'.print_r($param, true).'</span>
	</div>
	<div style="clear:both;"></div>
</pre>';
		}elseif($this->debug==='console'){
			echo trim($sql, "\n\r") . "\n" . print_r($param, true) . "\n";
		}
	}
	/*}}}*/
    /*fn oracle_bind_execute{{{*/
	function oracle_bind_execute($sql, $param=array(), $return_exec=false){
		$stid = ociparse($this->conn, $sql);

		foreach($param as $key=>$val){
			ocibindbyname($stid, ":".$key, $val);
			unset($val);
		}
		$tag = ociexecute($stid, $this->mode);
		//print_r(ocierror($stid));
		$this->print_sql($sql, $param, $tag);
		if($return_exec) return $tag;
		elseif($tag === false){
			$error = ocierror($stid);
			trigger_error("SQL Error:\n".$error['message']."\n".$error['sqltext']."\n".print_r($param, true) . "\n", E_USER_NOTICE);
			return false;
		}
		return $stid;
	}
    /*}}}*/

	/*fn beginTrans{{{*/
	function beginTrans(){
		$this->mode = OCI_DEFAULT;
	}
	/*}}}*/
	/*fn rollbackTrans{{{*/
	function rollbackTrans(){
		ocirollback();
		//$this->execute("rollback");
	}
	/*}}}*/
	/*fn commitTrans{{{*/
	function commitTrans(){
		ocicommit();
		//$this->execute("commit");
	}
	/*}}}*/

    /*fn getOne{{{*/
	function getOne($sql,$param=array()){
		$stid = $this->oracle_bind_execute($sql, $param);
		if($stid === false) return false;
		$dataList = array();
		ocifetchinto($stid, $row, OCI_NUM);
		ocifreestatement($stid);
		$row = array_values($row);
		return $row[0];
	}
    /*}}}*/

    /*fn getRow{{{*/
	function getRow($sql,$param=array()){
		$stid = $this->oracle_bind_execute($sql, $param);
		if($stid === false) return false;
		ocifetchinto($stid, $ret, OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS);
		ocifreestatement($stid);
		if(!$ret) return array();
		return array_change_key_case($ret);
	}
    /*}}}*/

	/* return a array contains the query results */
    /*fn getAll{{{*/
	function getAll($sql,$param=array()){
		$stid = $this->oracle_bind_execute($sql, $param);
		if($stid === false) return false;
		$dataList = array();
		while($tag = ocifetchinto($stid, $row, OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS)){
			$dataList[] = array_change_key_case($row);
		}

		ocifreestatement($stid);
		return array(
			'result'=>$dataList,
			'length'=>count($dataList)
		);
	}
    /*}}}*/
	/*fn execute{{{*/
	function execute($sql, $param = array()){
		return $this->oracle_bind_execute($sql, $param, true);
	}
	/*}}}*/
}
$a = new oracle4php4();
//$a->debug = 'console';
/*
$data = $a->execute(
	"
update ad_type
 set addtime = sysdate
 where id = :0
    or addtime = to_date(:1, 'yyyy-mm-dd hh24:mi:ss')
	", array('13138', '2011-10-21 10:02:10')
);
var_dump($data);
die;
 */
$data = $a->getRow(
	"
select m.order_num,
       t.fid,
       m.language,
       t.sha1,
       t.url_key,
       m.resource_type,
       t.name,
       m.fotm,
       to_char(m.addtime, 'yyyy-mm-dd') update_date,
       v.datasource_comment brief_comment,
       m.language || m.order_num uni_key,
       v.datasource_contenttime
  from ugc_media_resource t
  join media_resource m
    on t.fid = m.datasource_index
   and m.resource_type = 1
   and t.statusflag = 1
  join vod_datasource v
    on m.datasource_index = v.datasource_index
   and v.datasource_statusflag in (10, 30)
   and nvl(v.datasource_popt, 0) < 5
 where url_key is not null
   and m.sid => :test
   and nvl(m.language, 'NO') =
       (select nvl(language, 'NO')
          from (select m.language, count(1) c
                  from ugc_media_resource t
                  join media_resource m
                    on t.fid = m.datasource_index
                   and t.statusflag = 1
                  join vod_datasource v
                    on m.datasource_index = v.datasource_index
                   and v.datasource_statusflag in (10, 30)
                   and nvl(v.datasource_popt, 0) < 5
                 where url_key is not null
                   and m.sid = :test
                 group by m.language
                 order by c desc)
         where rownum = 1)
	", array('test'=>13138)
);
print_r($data);
/* vim: set fdm=marker tabstop=4 shiftwidth=4 softtabstop=4: */
